package cn.aloxc.stock.tools;

import org.codehaus.groovy.grails.commons.*
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.hssf.usermodel.HSSFSheet        
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFCell
import org.codehaus.groovy.grails.commons.GrailsDomainClass
import org.codehaus.groovy.grails.web.converters.ConverterUtil
import org.springframework.web.servlet.support.RequestContextUtils as RCU
import org.codehaus.groovy.grails.scaffolding.DomainClassPropertyComparator;
import org.springframework.context.MessageSource
import org.apache.poi.hssf.usermodel.HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFFont
import org.apache.poi.hssf.util.HSSFColor;
import cn.aloxc.stock.*;
import cn.aloxc.stock.context.SysContext as SC;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import groovy.sql.*;
class XlsExportService {
  public static void main(def args)
  {

    def domains = ["cn.aloxc.stock.Zcggstat","cn.aloxc.stock.Boyistat","cn.aloxc.stock.Fundstat"];
    //println SC.getPrpt("ZCGG_SDD_MAX");
    //println SC.getPrpt("zcgg_atype");
    //println SC.getPrpt("zcgg_atype_text")[0];

    //me.xlsExportData(null,null,domains)
    Expando a1 = new Expando();
    a1.name = "A1name";
    Expando a2 = new Expando();
    a2.name = "A2name";
    Expando a3 = new Expando();
    a3.name = "A3name";
    Expando b1 = new Expando();
    b1.name = "B1name";
    Expando b2 = new Expando();
    b2.name = "B2name";
    Expando b3 = new Expando();
    b3.name = "B3name";
    Expando c1 = new Expando();
    c1.name = "C1name";
    Expando c2 = new Expando();
    c2.name = "C2name";
    Expando c3 = new Expando();
    c3.name = "C3name";
    def map = ["091001":[a1,b1,c1], "091002":[a2,b2,c2], "091003":[a3,b3,c3]]
    
  }
  def createDataSheet(HSSFWorkbook wb,name,datas)
  {
    //println datas
        HSSFSheet sheet = wb.createSheet(name);
        HSSFRow row
        HSSFCell cell
        HSSFCellStyle style = wb.createCellStyle()
        HSSFFont font   =   wb.createFont()
        font.setFontHeightInPoints((short)12)
        //font.setFontName("����")
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
        style.setFont(font)
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
        style.setFillBackgroundColor(HSSFColor.ORANGE.index);
        style.setFillPattern(HSSFCellStyle.FINE_DOTS)
        row = sheet.createRow(0);/*table title*/
        cell = row.createCell((short)(0));/*rk*/
        cell.setCellStyle(style);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("RK");
        def rk = false;
        datas.eachWithIndex {data,dIdx ->
          def key = data.key;
          def mapValues = data.value;
          mapValues.eachWithIndex { value,vIdx ->
              row = sheet.createRow(vIdx+1);
          }
        }
        datas.eachWithIndex {data,dIdx ->
          def key = data.key;
          def mapValues = data.value;
          row = sheet.getRow(0);
          cell = row.createCell((short)(dIdx+1));
          cell.setCellStyle(style);
          cell.setCellType(HSSFCell.CELL_TYPE_STRING);
          cell.setCellValue(key);
          //println "mapValues:${mapValues[1]}";
          mapValues.eachWithIndex { value,vIdx ->
              row = sheet.getRow(vIdx+1);
              cell = row.createCell((short)(0))
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setCellValue((vIdx+1).toString()) ;
              cell = row.createCell((short)(dIdx+1))
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setCellValue(value) ;
          }
        }
    return wb;
  }
  def groovy.sql.Sql sql;
private void initConn()
{

    def driver = "com.mysql.jdbc.Driver";
    def db = "jdbc:mysql://localhost/stockstat?useUnicode=true&characterEncoding=gb2312";
    def user = "root";
    def pwd = "111111";
    sql = Sql.newInstance(db,user,pwd,driver);

}
  def xlsExportData(out, request, domains){
      initConn();
      HSSFWorkbook wb = new HSSFWorkbook();
      def sdf = new SimpleDateFormat("yyMMdd");
      def qsdf = new SimpleDateFormat("yyyy-MM-dd");
      domains.eachWithIndex{domain ,dmnIdx ->
        def smpDmn = domain.replaceAll("${SC.DOMAIN_ROOT}.","").toLowerCase(); //eg:zcggstat
        def preDmn = smpDmn.replaceAll("stat","");
        if(smpDmn == "zcggstat")
        {
          //println sc.getPrpt("${preDmn}_ATYPE");
          //println sc.getPrpt("${preDmn}_ATYPE");
          //println tools.StringUtil.unicodeToGB2312(SC.getPrpt("${preDmn.toUpperCase()}_ATYPE_TEXT").toString());
          //println SC.getPrpt("${preDmn.toUpperCase()}_ATYPE_TEXT");
          //println SC."${preDmn.toUpperCase()}_ATYPE_TEXT"
          //println SC.getPrpt("${preDmn}_ATYPE");

             SC.getPrpt("${preDmn}_ATYPE").eachWithIndex{ aTp , aIdx ->
               //println aTp
                def aTxt = SC.getPrpt("${preDmn}_ATYPE_TEXT")[aIdx];
                def atype = SC.getPrpt("${preDmn}_ATYPE")[aIdx];
                def sheetName = tools.StringUtil.unicodeToGB2312(SC.ACTION_TEXT[SC.ACTION.indexOf(smpDmn)]);
                sheetName = tools.StringUtil.unicodeToGB2312("${sheetName}-${aTxt}");
                            def params = new Expando()
                            params.max = 90;
                            params.a = atype;
                            params.s = 30;
                            def curDate = new Date() - params.s;
                            def zcggstatdataMap = new TreeMap();
                            0.step(params.s,1){
                              curDate++;
                              curDate.setHours(0);
                              curDate.setMinutes(0);
                              curDate.setSeconds(0);
                              def fList = [];
                              //println curDate
                              def sqlString = "select * from zcggstat where atype='${atype}' and statdate='${qsdf.format(curDate)}' order by id asc limit ${params.max}"
                               //println ""+sqlString;

                              sql.eachRow("select * from zcggstat where atype=? and statdate=? order by id asc limit ${params.max}",[atype,qsdf.format(curDate)]){ __query ->
                                fList.add(__query.name);
                              }
                              def dkey = sdf.format(curDate);
                              if(fList.size()>0)
                              {
                                zcggstatdataMap.put(dkey,fList)
                              }
                            };
                wb = createDataSheet(wb,sheetName,zcggstatdataMap);
                //println zcggstatdataMap
             }              
        }
        else
        {
          SC.getPrpt("${preDmn}_PERIOD").eachWithIndex{prd, pIdx ->
            def pTxt = SC.getPrpt("${preDmn}_PERIOD_TEXT")[pIdx];
            def period = SC.getPrpt("${preDmn}_PERIOD")[pIdx];
            if(["day","week"].indexOf(period)>-1)
            {
              SC.getPrpt("${preDmn}_ATYPE").eachWithIndex{aTp, aIdx ->
              def aTxt = SC.getPrpt("${preDmn}_ATYPE_TEXT")[aIdx];
              def atype = SC.getPrpt("${preDmn}_ATYPE")[aIdx];
              def sheetName = tools.StringUtil.unicodeToGB2312(SC.ACTION_TEXT[SC.ACTION.indexOf(smpDmn)]);
              //sheetName = tools.StringUtil.unicodeToGB2312("${sheetName}-${pTxt}-${aTxt}");
              sheetName = tools.StringUtil.unicodeToGB2312("${pTxt}-${aTxt}");
             
                            def params = new Expando()
                            params.max = 90;
                            params.a = atype;
                            params.s = 30;
                            def curDate = new Date() - params.s;
                            def dataMap = new TreeMap();
                            0.step(params.s,1){
                              curDate++;
                              curDate.setHours(0);
                              curDate.setMinutes(0);
                              curDate.setSeconds(0);
                              def fList = [];
                              def sqlString = "select * from ${smpDmn} where atype='${atype}' and statdate='${qsdf.format(curDate)}' and period='${period}' order by id asc limit ${params.max}"
                              sql.eachRow("select * from ${smpDmn} where atype=? and statdate=? and period=? order by id asc limit ${params.max}",[atype,qsdf.format(curDate),period]){__query ->
                                  fList.add(__query.name);
                              }
                              def dkey = sdf.format(curDate);
                              if(fList.size()>0)
                              {
                                dataMap.put(dkey,fList)
                              }
                            };

              wb = createDataSheet(wb,sheetName,dataMap);
              //println "${period}--${atype}"
              //println dataMap;
            }
            }
          }
        }
      }
        wb.write(out);
        out.close()
  }

//    def messageSource
    MessageSource messageSource
    boolean transactional = true
//    def config = ConfigurationHolder.config
//    def domainName
    def xlsExport(out, request, domain, datas) {
        def excludedProps = ['id', 'version']
        def column = []
        def titles = []
        def outProperties
        def locale = RCU.getLocale(request)
        def args
        def domainName = domain.toLowerCase()
        //domain = "${SC.DOMAIN_ROOT}.${domain}"
        GrailsDomainClass domainClass = ConverterUtil.getDomainClass("${SC.DOMAIN_ROOT}.${domain}");
      
        if (domainClass != null) {
            outProperties = domainClass.properties.findAll {!excludedProps.contains(it.name)}
        }
        titles = outProperties.name
        Collections.sort(outProperties, new DomainClassPropertyComparator(domainClass))
        /*outProperties.each {
            column << "${it.name}"
            def text = messageSource.getMessage(
                    "${domainName}.${it.name}".toString(),
                    args == null ? null : args.toArray(),
                    "${domainName}.${it.name}".toString(),
                    locale)
            titles << text
        } */
        HSSFWorkbook wb = new HSSFWorkbook();
        def sheetName = tools.StringUtil.unicodeToGB2312(SC.ACTION_TEXT[0]);
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row
        HSSFCell cell
        HSSFCellStyle style = wb.createCellStyle()
        HSSFFont font   =   wb.createFont()
        font.setFontHeightInPoints((short)12)
        font.setFontName("����")
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
        style.setFont(font)
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
        style.setFillBackgroundColor(HSSFColor.ORANGE.index);
        style.setFillPattern(HSSFCellStyle.FINE_DOTS)
        // set the title
        row = sheet.createRow((short) 0);
        cell = row.createCell((short) (0));

        cell.setCellStyle(style)
     // style.setFillBackgroundColor 
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("RK");
        titles.eachWithIndex {title, i ->
            cell = row.createCell((short) (i+1));
            cell.setCellStyle(style)
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(title);    
        }

        // set the data
        datas.eachWithIndex {data, i ->
            row = sheet.createRow((short) (i+1));
            row.createCell((short)(0)).setCellValue(i+1) ;

            titles.eachWithIndex {p, j ->
                row.createCell((short) (j+1)).setCellValue(data."${p}");

            }

        }
           
        // Write the output to a file
        wb.write(out);
        out.close()

    }
}

